import getopt
import os
import sys
import traceback
from multiprocessing import Pool

import pandas as pd

from air_web.data_platform import mysql_con, sql_engine

path = "/home/zshield/zexin_test/hebei_kf/websvr/src/result_data"


def proc_cons_gb():
    org_path = os.path.join(path, "real_org_no.csv")
    org_df = pd.read_csv(org_path, header=0, dtype=str)
    cons_path = os.path.join(path, "公变用户展示（683户）.xlsx")
    df_683 = pd.read_excel(cons_path, header=0, dtype=str, index_col=0)
    df_683 = df_683[
        ["CONS_NO（总表编号）", "ID", "CONS_NAME（用户名称）", "ORG_NO（供电公司编号）"]
    ]
    df_683.rename(columns={"ORG_NO（供电公司编号）": "ORG_NO_683"}, inplace=True)

    df_gb = pd.read_csv(f"{path}/filter_field_gb.csv", dtype=str)
    df_gb_res = df_683.merge(
        df_gb[
            [
                "CCONS_CONS_NO",
                "ON5",
                "CITY",
                "ON7",
                "CM_T_FACTOR",
                "OORG_ORG_NAME",
                "OORG_ORG_NO",
            ]
        ],
        left_on="CONS_NO（总表编号）",
        right_on="CCONS_CONS_NO",
        how="left",
    )

    df_gb_res["TYPE_CODE_SORT"] = 2
    df_gb_res["负控标签分类"] = "城市居民"
    df_gb_res.rename(
        columns={
            "CITY": "SHI",
            "负控标签分类": "TYPE_CODE",
            "ID": "emp_id",
            "CM_T_FACTOR": "t_factor",
            "CONS_NO（总表编号）": "CONS_NO",
            "CONS_NAME（用户名称）": "CONS_NAME",
        },
        inplace=True,
    )
    df_gb_res.drop(["CCONS_CONS_NO"], inplace=True, axis=1)

    df_gb_res["SHI"] = "石家庄市"
    df_gb_res["ON5"] = "13401"

    def gen_on7(x):
        if not x["ON7"]:
            return x["ORG_NO_683"][:7]
        return x["ON7"]

    df_gb_res["ON7"] = df_gb_res.apply(gen_on7, axis=1)

    df_gb_res = df_gb_res.merge(
        org_df[["ORG_NO", "ORG_NAME"]],
        left_on="ON7",
        right_on="ORG_NO",
        how="left",
    )
    df_gb_res.rename(columns={"ORG_NAME": "XIAN"}, inplace=True)
    df_gb_res.drop("ORG_NO", axis=1, inplace=True)
    df_gb_res.rename(
        columns={
            "OORG_ORG_NAME": "ORG_NAME",
            "OORG_ORG_NO": "ORG_NO",
        },
        inplace=True,
    )
    df_gb_res.drop("ORG_NO_683", axis=1, inplace=True)
    df_gb_res.columns = map(str.lower, df_gb_res.columns)

    print(df_gb_res.columns)
    print(df_gb_res)
    df_gb_res.drop_duplicates(subset=["cons_no"], inplace=True)
    print(df_gb_res)

    sql_engine.insert_df(df_gb_res, "c_cons_all")

    # filter_df.to_csv(path + 'cons_gb.csv', index=False)


def proc_cons_zb():
    org_path = os.path.join(path, "real_org_no.csv")
    org_df = pd.read_csv(org_path, header=0, dtype=str)

    cons_path = os.path.join(path, "专变用户关联结果.csv")
    df = pd.read_csv(cons_path, header=0, dtype=str, index_col=0)
    filter_df = df
    filter_df = filter_df[
        [
            "EMP_ID",
            "CONS_NO",
            "CCONS_CONS_NAME",
            "ON5",
            "CITY",
            "ON7",
            "负控标签分类",
            "CM_T_FACTOR",
            "OORG_ORG_NAME",
            "OORG_ORG_NO",
        ]
    ]
    filter_df.rename(
        columns={
            "CITY": "SHI",
            "负控标签分类": "TYPE_CODE",
            "EMP_ID": "emp_id",
            "CM_T_FACTOR": "t_factor",
            "CONS_NO": "CONS_NO",
            "CCONS_CONS_NAME": "CONS_NAME",
        },
        inplace=True,
    )
    filter_df["TYPE_CODE_SORT"] = 1
    filter_df["SHI"] = filter_df["SHI"].str.replace("供电公司", "市")
    filter_df = filter_df.merge(
        org_df[["ORG_NO", "ORG_NAME"]],
        left_on="ON7",
        right_on="ORG_NO",
        how="left",
    )
    filter_df.rename(columns={"ORG_NAME": "XIAN"}, inplace=True)
    filter_df.drop("ORG_NO", axis=1, inplace=True)
    filter_df.rename(
        columns={
            "OORG_ORG_NAME": "ORG_NAME",
            "OORG_ORG_NO": "ORG_NO",
        },
        inplace=True,
    )

    filter_df.columns = map(str.lower, filter_df.columns)
    print(filter_df.columns)
    # filter_df.to_csv(path + 'cons_zb.csv', index=False)
    sql_engine.insert_df(filter_df, "c_cons_all")


# proc_cons_gb()


def get_all_cons_csv():
    df = sql_engine.read_df_by_sql("select * from c_cons_all")

    df.to_csv(path + "/c_cons_csv_gb_zb.csv", index=False)


get_all_cons_csv()
